import numpy as np
import pandas as pd

# brand_file_path = '../../data/餐饮连锁品牌数据.xlsx'
cater_file_path = '../../data/raw data/餐饮连锁数据.xlsx'
sheet_names=['门店信息','菜品信息','营销记录','顾客评价']
sheet_name=sheet_names[1]
df_dish = pd.read_excel(cater_file_path, sheet_name)

print("\n【空值统计】")
print(df_dish.isnull().sum())
print("======================")
# --- 测试1：查看哪些列空值最多 ---
missing_rate = df_dish.isnull().mean().sort_values(ascending=False)
print("\n【空值比例（Top 10）】")
print(missing_rate.head(10))
print("======================")
# --- 处理方式：删除含有空值的行 ---
before_rows = df_dish.shape[0]# 记录删除前的行数
df_dish.dropna(inplace=True)# 删除包含空值的行
after_rows = df_dish.shape[0]# 记录删除后的行数
print(f"\n【空值处理】已删除 {before_rows - after_rows} 行包含空值的数据。")

# --- 验证：是否还有空值 ---
print("\n【空值处理后验证】")
print(df_dish.isnull().sum().sum())  # 0 表示处理完毕
print('======================')
print('======================')
# ======================================
# 3️⃣ 检查并处理重复值
# ======================================
print("\n【重复值检测】")
print(df_dish.duplicated().sum())
print('================================')
# --- 测试2：查看重复行 ---
if df_dish.duplicated().sum() > 0:
    print(df_dish[df_dish.duplicated()])

# 删除重复行
df_dish.drop_duplicates(inplace=True)

# --- 验证：重复值是否清除 ---
print("\n【重复值处理后验证】")
print(df_dish.duplicated().sum())
print('======================')
print('======================')
print('=============================')
# ======================================
# 4️⃣ 识别并修正异常值
# ======================================

# --- 测试3：查看数值列的分布 ---
print("\n【数值列统计描述】")
print(df_dish.describe())

# --- 示例：处理异常的“销售额”列 ---

print('单价（元）--------------------------------------')
outer='单价(元)'
if outer in df_dish.columns:
    mean_sales = df_dish[outer].mean()
    # 计算销售额的标准差（用于异常值检测）
    std_sales = df_dish[outer].std()
    # 定义异常值的阈值（3倍标准差）
    upper = mean_sales + 3 * std_sales# 上边界
    lower = mean_sales - 3 * std_sales# 下边界

    # 标记异常值
    outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数
    median_sales = df_dish[outer].median()
    df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_sales
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_sales}。")

print('口味评分-----------------------------------------')
max_score=5.0
min_score=0.0
outer='口味评分'
if outer in df_dish.columns:

    # 定义异常值的阈值max_score和min_score
    upper = max_score# 上边界
    lower = min_score# 下边界

    # 标记异常值
    outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数
    median_sales = df_dish[outer].median()
    df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_sales
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_sales}。")
# print('分量评分-----------------------------------------')
print('分量评分-----------------------------------------')
outer='分量评分'
if outer in df_dish.columns:
    # 定义异常值的阈值max_score和min_score
    upper = max_score  # 上边界
    lower = min_score  # 下边界

    # 标记异常值
    outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数
    median_sales = df_dish[outer].median()
    df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_sales
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_sales}。")
print('颜值评分-----------------------------------------')
outer='颜值评分'
if outer in df_dish.columns:
    # 定义异常值的阈值max_score和min_score
    upper = max_score  # 上边界
    lower = min_score  # 下边界

    # 标记异常值
    outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数
    median_sales = df_dish[outer].median()
    df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_sales
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_sales}。")
print('上菜速度评分-----------------------------------------')
outer='上菜速度评分'
if outer in df_dish.columns:
    # 定义异常值的阈值max_score和min_score
    upper = max_score  # 上边界
    lower = min_score  # 下边界

    # 标记异常值
    outliers = df_dish[(df_dish[outer] > upper) | (df_dish[outer] < lower)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数
    median_sales = df_dish[outer].median()
    df_dish.loc[(df_dish[outer] > upper) | (df_dish[outer] < lower), outer] = median_sales
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_sales}。")

print('======================================')
print('======================================')
# ======================================
# 6️⃣ 导出清洗后的数据
# ======================================
clean_path = '../../data/cleared data/餐饮连锁数据_菜品信息_cleaned.xlsx'
df_dish.to_excel(clean_path, index=False)
print(f"\n✅ 数据清洗完成，已保存至 {clean_path}")